package rohith.custom;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import oracle.jbo.CSMessageBundle;
import oracle.jbo.PCollException;
import oracle.jbo.common.Diagnostic;
import oracle.jbo.pcoll.JDBCPersistManager;
import oracle.jbo.pcoll.PCollection;

/**
 * @author: Rohith Puchalapalli
 */
public class CustomPersistManager extends JDBCPersistManager
{
  public DB2400PersistManager()
  {
    super();
  }
  public int getMaxTabNameLength()
  {
    return 18 - ("_SEQ").length();
  }


  protected String sqlCreateControlTable(String controlTabName)
  {
    
   
    return "create table " + controlTabName +
      "(tabname varchar(128) not null, rowcreatedate timestamp, createdate timestamp, updatedate timestamp, " +
      "constraint " + controlTabName + "_pk primary key (tabname))";
  }


  protected int sqlTableExistsErrorCode()
  {
    return DB2_ERROR_TABLE_ALREADY_EXISTS;
  }


  protected void dropTable(String tabName)
  {
    synchronized (this)
    {
      Statement stmt = null;

      try
      {
        stmt = getConnection().createStatement();
      }
      catch (SQLException ex)
      {
        if (Diagnostic.isOn())
        {
          Diagnostic.println("**createStatement failed**");
          Diagnostic.printStackTrace(ex);
        }

        PCollException.throwException(CSMessageBundle.class,
                                      CSMessageBundle.EXC_PCOLL_CREATE_TAB,
                                      new Object[]
            { tabName }, ex);
      }

      try
      {
        String sqlStmt = "drop table \"" + tabName + "\"";
        stmt.execute(sqlStmt);

        if (PCollection.mDebugOn && Diagnostic.isOn())
        {
          Diagnostic.println("**dropTable** drop table, tabname=" +
                             tabName);
        }
      }
      catch (SQLException ex)
      {
        if (PCollection.mDebugOn && Diagnostic.isOn())
        {
          Diagnostic.println("**dropTable** drop table failed, tabname=" +
                             tabName);
        }
      }

      String keyInfoTabName = tabName + "_ky";

      try
      {
        String sqlStmt = "drop table \"" + keyInfoTabName + "\"";
        stmt.execute(sqlStmt);

        if (PCollection.mDebugOn && Diagnostic.isOn())
        {
          Diagnostic.println("**dropTable** drop table, tabname=" +
                             keyInfoTabName);
        }
      }
      catch (SQLException ex)
      {
        if (PCollection.mDebugOn && Diagnostic.isOn())
        {
          Diagnostic.println("**dropTable** drop table failed, tabname=" +
                             keyInfoTabName);
        }
      }

      if (isPersistent())
      {
        try
        {
          String sqlStmt = "drop sequence " + tabName + "_SEQ restrict";
          stmt.execute(sqlStmt);

          if (PCollection.mDebugOn && Diagnostic.isOn())
          {
            Diagnostic.println("**dropTable** dropped sequence");
          }
        }
        catch (SQLException ex)
        {
          if (PCollection.mDebugOn && Diagnostic.isOn())
          {
            Diagnostic.println("**dropTable** drop sequence failed");
          }
        }
      }

      commit();
    }
  }


  protected String sqlAddKeyContColumn(String tabName)
  {
    // return "alter table \"" + tabName + "\" add (keycont blob)";
    return null;
  }


  protected String sqlDropKeyInfoTable(String keyInfoTabName)
  {
    return "drop table \"" + keyInfoTabName + "\"";
  }


  protected String sqlCreateKeyInfoTable(String keyInfoTabName)
  {
    return "create table \"" + keyInfoTabName +
      "\"(collid decimal(10), keyid decimal(20), keyhash decimal(20), id decimal(20))";
  }


  protected String sqlCreateKeyInfoKeyIndex(String keyInfoIndexName,
                                            String keyInfoTabName)
  {
    return "create index \"" + keyInfoIndexName + "\" on \"" +
      keyInfoTabName + "\" (collid, keyid, keyhash)";
  }


  protected String sqlCreateKeyInfoIdIndex(String keyInfoIndexName,
                                           String keyInfoTabName)
  {
    return "create index \"" + keyInfoIndexName + "\" on \"" +
      keyInfoTabName + "\" (collid, id)";
  }


  protected String sqlCreateTable(String tabName)
  {
    
    return "create table \"" + tabName +
      "\"(id decimal(20) not null, parentid decimal(20), collid decimal(10) not null, " +
      "keycontlen decimal(20), contentlen decimal(20), contentidx decimal(10) not null, content blob(1M) , contentstatus decimal(1), " +
      ((isPersistent())?
       "creation_date timestamp DEFAULT current timestamp, ": "") +
      "constraint " + tabName +
      "_pk1 primary key (collid, id, contentidx))";
  }


  protected String sqlCreateIndex(String tabName)
  {
    return "create index \"" + tabName + "_ci\" on \"" + tabName +
      "\" (collid)";
  }


  protected long sqlNextSequenceVal(String seqName)
  {
    Statement stmt = null;
    ResultSet rslt = null;
    long nextVal = -1;

    try
    {
      stmt = getConnection().createStatement();
      rslt = stmt.executeQuery("values nextval for " + seqName);

      if (!rslt.next())
      {
        PCollException.throwException(CSMessageBundle.class,
                                      CSMessageBundle.EXC_PCOLL_GETCURTIME,
                                      null, null);
      }

      nextVal = rslt.getLong(1);
    }
    catch (Exception ex)
    {
      PCollException.throwException(CSMessageBundle.class,
                                    CSMessageBundle.EXC_PCOLL_SEQ_NEXTVAL,
                                    new Object[]
          { seqName }, ex);
    }
    finally
    {
      closeResultSet(rslt);
      rslt = null;
      closeStmt(stmt);
      stmt = null;
    }

    return nextVal;
  }


  protected Date getCurrentTimeFromDb()
  {
    Statement stmt = null;
    ResultSet rslt = null;
    Date tStamp = null;

    try
    {
      stmt = getConnection().createStatement();
      rslt = stmt.executeQuery("values current timestamp");

      if (!rslt.next())
      {
        PCollException.throwException(CSMessageBundle.class,
                                      CSMessageBundle.EXC_PCOLL_GETCURTIME,
                                      null, null);
      }

      tStamp = (Date) rslt.getObject(1);
    }
    catch (Exception ex)
    {
      PCollException.throwException(CSMessageBundle.class,
                                    CSMessageBundle.EXC_PCOLL_GETCURTIME,
                                    null, ex);
    }
    finally
    {
      closeResultSet(rslt);
      rslt = null;
      closeStmt(stmt);
      stmt = null;
    }

    return tStamp;
  }


  protected String sqlDeleteControlRow(String controlTabName,
                                       String tabName)
  {
    return "delete from " + controlTabName + " where tabname=\'" +
      tabName + "\'";
  }


  protected String sqlUpdateControlRow(String controlTabName,
                                       String tabName, String dateColName)
  {
    return "update " + controlTabName + " set " + dateColName +
      "=current timestamp where tabname=\'" + tabName + "\'";
  }


  protected String sqlLockControlRow(String controlTabName, String tabName)
  {
    return "select tabname, updatedate from " + controlTabName +
      " where tabname=\'" + tabName + "\' for update";
  }


  protected String sqlInsertControlRow(String controlTabName,
                                       String tabName)
  {
    return "insert into " + controlTabName + " values (\'" + tabName +
      "\', current timestamp, current timestamp, current timestamp)";
  }


  protected int sqlLockConflictErrorCode()
  {
    return DB2_ERROR_LOCK_CONFLICT;
  }


  protected int contentChunkLength()
  {
    return DB2_CHUNK_LENGTH;
  }


  protected String sqlDeleteKeyInfo(String keyInfoTabName, long id)
  {
    String sqlStmt =
      "delete from \"" + keyInfoTabName + "\" where collid=?";

    if (id != 0)
    {
      sqlStmt += " and id=?";
    }
    return sqlStmt;
  }


  protected void bindDeleteKeyInfo(PreparedStatement pStmt, long id,
                                   int collid)
    throws SQLException
  {
    pStmt.setInt(1, collid);
    if (id != 0)
    {
      pStmt.setLong(2, id);
    }
  }


  protected String sqlInsertKeyInfo(String keyInfoTabName)
  {
    return "insert into \"" + keyInfoTabName + "\" values (?, ?, ?, ?)";
  }


  protected void bindInsertKeyInfo(PreparedStatement pStmt, long id,
                                   int collid, int keyId, int keyHash)
    throws SQLException
  {
    pStmt.setInt(1, collid);
    pStmt.setInt(2, keyId);
    pStmt.setInt(3, keyHash);
    pStmt.setLong(4, id);
  }


  protected String sqlInsertDataRow(String tabName, long id, long parid,
                                    int collid)
  {
    String sqlStmt =
      "insert into \"" +tabName + "\" (id, parentid, collid, keycontlen, contentlen, contentidx, content, contentstatus) values (?, ?, ?, ?, ?, ?, ?, ?)";
    return sqlStmt;
  }


  protected void bindInsertDataRow(PreparedStatement pStmt, long id,
                                   long parid, int collid, int keyContLen,
                                   int contentLen, int contentIdx,
                                   byte[] chunk, int contentStatus)
    throws SQLException
  {
      pStmt.setLong(1, id);
      pStmt.setLong(2, parid);
      pStmt.setInt(3, collid);
      pStmt.setInt(4, keyContLen);
      pStmt.setInt(5, contentLen);
      pStmt.setInt(6, contentIdx);
      pStmt.setBytes(7,  chunk);
      pStmt.setInt(8, contentStatus);
  }


  protected String sqlDeleteOldChunksDataRow(String tabName, long id,
                                             int collid, int begIndx,
                                             int endIndx)
  {
    return "delete from \"" + tabName +
      "\" where id=? and collid=? and contentidx >= ? and contentidx <= ?";
  }


  protected void bindDeleteOldChunksDataRow(PreparedStatement pStmt,
                                            long id, int collid,
                                            int begIndx, int endIndx)
    throws SQLException
  {
    pStmt.setLong(1, id);
    pStmt.setInt(2, collid);
    pStmt.setInt(3, begIndx);
    pStmt.setInt(4, endIndx);
  }


  protected String sqlUpdateDataRow(String tabName, long id, long parid,
                                    int collid)
  {
    return "update \"" + tabName +
      "\" set parentid=?, keycontlen=?, contentlen=?, content=?, contentstatus=? where id=? and collid=? and contentidx=?";
  }


  protected void bindUpdateDataRow(PreparedStatement pStmt, long id,
                                   long parid, int collid, int keyContLen,
                                   int contentLen, int contentIdx,
                                   byte[] chunk, int contentStatus)
    throws SQLException
  {
    pStmt.setLong(1, parid);
    pStmt.setInt(2, keyContLen);
    pStmt.setInt(3, contentLen);
    pStmt.setBytes(4, chunk);
    pStmt.setInt(5, contentStatus);
    pStmt.setLong(6, id);
    pStmt.setInt(7, collid);
    pStmt.setInt(8, contentIdx);
  }


  protected String sqlChangeParentDataRow(String tabName, long id,
                                          long parid, int collid)
  {
    return "update \"" + tabName +
      "\" set parentid=? where id=? and collid=?";
  }


  protected void bindChangeParentDataRow(PreparedStatement pStmt, long id,
                                         long parid, int collid)
    throws SQLException
  {
    _logger.debug(" ## parid "+ parid+" id "+id+" collid "+collid);
    pStmt.setLong(1, parid);
    pStmt.setLong(2, id);
    pStmt.setInt(3, collid);
  }


  protected String sqlDeleteDataRow(String tabName, long id, int collid)
  {
    return "delete from \"" + tabName + "\" where id=? and collid=?";
  }


  protected void bindDeleteDataRow(PreparedStatement pStmt, long id,
                                   int collid)
    throws SQLException
  {
    pStmt.setLong(1, id);
    pStmt.setInt(2, collid);
  }


  protected String sqlDeleteAllDataRows(String tabName, int collid)
  {
    return "delete from \"" + tabName + "\" where collid=?";
  }


  protected void bindDeleteAllDataRows(PreparedStatement pStmt, int collid)
    throws SQLException
  {
    pStmt.setInt(1, collid);
  }


  protected String sqlRetrieveDataRow(String tabName, long id, int collid,
                                      boolean justLen, boolean keyonly)
  {
    String sqlStmt;
    String keyOnlyClause = " and contentstatus <= 2";

    if (justLen)
    {
      sqlStmt =
          "select keycontlen, contentlen from \"" + tabName + "\" where id=? and collid=?";
    }
    else
    {
      sqlStmt =
          "select parentid, keycontlen, contentlen, content from \"" +
          tabName + "\" where id=? and collid=?";
    }

    if (keyonly)
    {
      sqlStmt += keyOnlyClause;
    }

    sqlStmt += " order by collid, id, contentidx";
    return sqlStmt;
  }


  protected void bindRetrieveDataRow(PreparedStatement pStmt, long id,
                                     int collid, boolean justLen)
    throws SQLException
  {
    pStmt.setLong(1, id);
    pStmt.setInt(2, collid);
  }


  protected byte[] getContentDataRow(ResultSet rslt, long[] idRet,
                                     long[] parentIdRet,
                                     int[] contentLenRet, boolean justLen)
    throws SQLException
  {
    int colIndx = 1;

    if (justLen)
    {
      contentLenRet[0] = rslt.getInt(colIndx++);
      contentLenRet[1] = rslt.getInt(colIndx++);

      return null;
    }
    else
    {
      if (idRet != null)
      {
        idRet[0] = rslt.getLong(colIndx++);
      }

      if (parentIdRet != null)
      {
        parentIdRet[0] = rslt.getLong(colIndx++);
      }

      contentLenRet[0] = rslt.getInt(colIndx++);
      contentLenRet[1] = rslt.getInt(colIndx++);

      return rslt.getBytes(colIndx++);
    }
  }
  protected String sqlRetrieveIdsWithKey(String tabName,
                                         String keyInfoTabName, int keyId,
                                         Object keyval, int collid)
  {
    return "select k.id, t.parentid, t.keycontlen, t.contentlen, t.content from \"" +
      keyInfoTabName + "\" k, \"" + tabName +
      "\" t where k.collid=? and k.keyid=? and k.keyhash=? and k.collid=t.collid and k.id=t.id and contentstatus <= 2 order by t.collid, t.id, t.contentidx";
  }
  protected void bindRetrieveIdsWithKey(PreparedStatement pStmt, int keyId,
                                        Object keyVal, int keyHash,
                                        int collid)
    throws SQLException
  {
    pStmt.setInt(1, collid);
    pStmt.setInt(2, keyId);
    pStmt.setInt(3, keyHash);
  }

}
